import sqlite3

from common.config import DBPath


# sql 语句处理
def get_sql_insert_str(table_name, col_list):
    """
    获取添加数据的sql语句字符串
    :param table_name: 表名
    :param col_list: 列名列表
    :return: sql语句字符串
    """
    if col_list:
        col_str = ",".join(col_list)
        para_str = ",".join(["?"] * len(col_list))
        add_str = f'INSERT INTO {table_name} ({col_str}) values ( {para_str} )'
        return add_str


def get_update_str(table_name, col_list: list) -> str:
    """
    获取更新数据语句, 查询语句为通过id查询并更新
    参数：
    table_name -- 表名
    col_list  -- 需更新的列名，不含id
    """
    if len(col_list) > 0:
        set_value = []
        para_value = []

        for index, col in enumerate(col_list):
            set_value.append(f"{col}=?")
            para_value.append("?")
        para_str = ", ".join(set_value)
        sql_cmd = f"update {table_name} set {para_str} WHERE id = ?"
        return sql_cmd


def create_conn(db_path=None):
    if not db_path:
        db_path = DBPath

    conn = sqlite3.connect(db_path, check_same_thread=False)
    return conn


def get_col_name(tb_name):
    """
    获取表的列名
    tb_name:数据表的名字
    返回值：list[(0, 'id', 'INTEGER', 1, None, 1)]
    """
    conn = create_conn()
    cur = conn.cursor()
    cur.execute(f"PRAGMA table_info({tb_name})")
    col_list = []
    for data in cur:
        col_list.append(data[1])
    cur.close()
    conn.close()
    return col_list


def insert_one_row(tb_name, col_list, value):
    """
    功能：添加一行数据
    参数：
    tb_name -- 表名
    col_list -- 需要添加的列名
    value   ---与列名一一对应的值
    返回值：
    新添加行的id
    """
    insert_str = get_sql_insert_str(tb_name, col_list)
    conn = create_conn()
    cur = conn.cursor()
    cur.execute(insert_str, value)
    conn.commit()
    new_id = cur.lastrowid
    cur.close()
    conn.close()
    return new_id


def insert_rows(tb_name, col_list, values):
    """
    功能：添加多行数据
    参数：
    tb_name -- 表名
    col_list -- 需要添加的列名
    values   ---与列名一一对应的数据列表
    返回值：
    新添加行的最后一行的id
    """
    insert_str = get_sql_insert_str(tb_name, col_list)
    conn = create_conn()
    cur = conn.cursor()
    cur.executemany(insert_str, values)
    conn.commit()
    new_id = cur.lastrowid
    cur.close()
    conn.close()
    return new_id


def get_update_value(value: list, id_index):
    """把id放在数据列表的最后面"""
    data_id = value[id_index]
    value.pop(id_index)
    value.append(data_id)
    return value


def get_update_values(values: list, id_index):
    update_values = []
    for value in values:
        update_values.append(get_update_value(value, id_index))
    return update_values


def update_one_row(tb_name, col_list: list, value):
    """
    更新一条数据
    tb_name
    col_list   含id
    value
    """
    id_index = col_list.index("id")
    col_list.pop(id_index)
    sql_str = get_update_str(tb_name, col_list)
    update_value = get_update_value(value, id_index)
    execute_one(sql_str, update_value)


def update_rows(tb_name, col_list: list, values):
    """
    更新一条数据
    tb_name
    col_list  含id
    value
    """
    id_index = col_list.index("id")
    col_list.pop(id_index)
    sql_str = get_update_str(tb_name, col_list)
    update_values = get_update_values(values, id_index)
    execute_many(sql_str, update_values)


def execute_many(sql_str_data, values):
    conn = create_conn()
    cur = conn.cursor()
    cur.executemany(sql_str_data, values)
    conn.commit()
    cur.close()
    conn.close()


def execute_one(sql_str, value=None):
    conn = create_conn()
    cur = conn.cursor()
    if value:
        cur.execute(sql_str, value)
    else:
        cur.execute(sql_str)
    conn.commit()
    cur.close()
    conn.close()


def delete_by_id_list(table_name, id_list):
    if id_list:
        sql_str = f'DELETE FROM {table_name} WHERE id = ?'
        execute_many(sql_str, id_list)


def delete_one_by_id(self, tb_name, data_id):
    if data_id:
        sql_str = f'DELETE FROM {tb_name} WHERE id = ?'
        execute_one(sql_str, id)


def find_one(self, tb_name, condition):
    sql_str = f"select * form {tb_name} where {condition}"

    conn = create_conn()
    cur = conn.cursor()
    cur.execute(sql_str)
    data = cur.fetchone()
    cur.close()
    conn.close()
    return data


def find_many(self, tb_name, condition):
    sql_str = f"select * form {tb_name}"
    if condition:
        sql_str += f"  where {condition}"

    conn = create_conn()
    cur = conn.cursor()
    cur.execute(sql_str)
    datas = cur.fetchall()
    cur.close()
    conn.close()
    return datas


def insert_by_dict(tb_name, data_dict: dict):
    """通过字典数据更新数据表"""
    if data_dict:
        col_name_list = []
        value_list = []
        for key in data_dict.keys():
            col_name_list.append(key)
            value_list.append(data_dict[key])
        return insert_one_row(tb_name, col_name_list, value_list)
    return 0
